//
//  WalletLocalData.m
//  MyWallet
//
//  Created by 小肥羊 on 15/7/20.
//  Copyright (c) 2015年 小肥羊. All rights reserved.
//

#import "WalletLocalData.h"

#warning 从装的时候改下RecordDtate命名，多了一个t

@implementation WalletLocalData

#pragma mark - 单例
+ (WalletLocalData *)sharedManager;
{
    static WalletLocalData *sharedAccountManagerInstance = nil;
    static dispatch_once_t predicate;
    dispatch_once(&predicate, ^{
        sharedAccountManagerInstance = [[self alloc] init];
        [sharedAccountManagerInstance initValue];
    });
    return sharedAccountManagerInstance;
}

- (void)initValue
{
    NSLog(@"%@",DATABASE_PATH);
    self.db = [FMDatabase databaseWithPath:DATABASE_PATH];
    [self.db open];
    //创建纪录表
    NSString *createRecordTable = @"CREATE TABLE IF NOT EXISTS 'RecordTable' ('RecordDtate' DATETIME PRIMARY KEY NOT NULL UNIQUE, 'MoneyFrom' VARCHAR, 'CheckoutType' VARCHAR, 'Money' MONEY, 'MoneyFor' VARCHAR, 'Remarks' VARCHAR)";
    if ([self.db executeUpdate:createRecordTable]) {
        NSLog(@"创建碎片数据表");
    } else {
        NSLog(@"创建碎片数据表失败");
    }
    [self upDataWithMessage:@"创建表"];
    [self.db close];
}

#pragma mark - 开启关闭
+ (BOOL)openDB
{
    if ([[WalletLocalData sharedManager].db open]) {
        NSLog(@"数据库打开");
        [[WalletLocalData sharedManager] upDataWithMessage:@"数据库打开"];
        return YES;
    } else {
        NSLog(@"数据库打开失败");
        return NO;
    }
}
+ (BOOL)closeDB
{
    if ([[WalletLocalData sharedManager].db close]) {
        NSLog(@"数据库关闭");
        return YES;
    } else {
        NSLog(@"数据库关闭失败");
        return NO;
    }
}

#pragma mark - 数据操作
/*! @brief 添加纪录 */
- (BOOL)addRecord:(NSDate *)recordDate MoneyFrom:(NSString *)moneyFrom CheckoutType:(BOOL)checkoutType Money:(float)money MoneyFor:(NSString *)moneyFor Remarks:(NSString *)remarks
{
    NSString *addRecord = @"INSERT INTO 'RecordTable' ('RecordDtate','MoneyFrom','CheckoutType','Money','MoneyFor','Remarks') VALUES (?,?,?,?,?,?)";
    if ([self.db executeUpdate:addRecord,recordDate,moneyFrom,@(checkoutType),[NSNumber numberWithFloat:money],moneyFor,remarks]) {
        NSLog(@"添加");
        [self upDataWithMessage:@"添加数据"];
        return YES;
    } else {
        NSLog(@"添加失败");
        return NO;
    }
}
/*! @brief 删除记录 */
- (BOOL)deleteRecord:(NSDate *)recordDate
{
    NSString *deleteRecord = @"DELETE FROM 'RecordTable' Where RecordDtate = ?";
    if ([self.db executeUpdate:deleteRecord,recordDate]) {
        NSLog(@"删除");
        [self upDataWithMessage:@"删除数据"];
        return YES;
    } else {
        NSLog(@"删除失败");
        return NO;
    }
}
/*! @brief 修改纪录 */
- (BOOL)modifyRecord:(NSDate *)recordDate WithNewDate:(NSDate *)newDate MoneyFrom:(NSString *)moneyFrom CheckoutType:(BOOL)checkoutType Money:(float)money MoneyFor:(NSString *)moneyFor Remarks:(NSString *)remarks {
    NSString *modifyRecord = @"update RecordTable set RecordDtate = ? , MoneyFrom = ? , CheckoutType = ? , Money = ? , MoneyFor = ? , Remarks = ? Where RecordDtate = ?";
    if ([self.db executeUpdate:modifyRecord,newDate,moneyFrom,@(checkoutType),[NSNumber numberWithFloat:money],moneyFor,remarks,recordDate]) {
        NSLog(@"修改");
        [self upDataWithMessage:@"修改数据"];
        return YES;
    } else {
        NSLog(@"修改失败");
        return NO;
    }
}

#pragma mark - 查找
- (NSMutableArray *)recordArrayWithCount:(NSInteger)count MoneyFrom:(NSString *)moneyFrom
{
    NSMutableArray *array = [[NSMutableArray alloc] init];
    FMResultSet *record = [self.db executeQuery:[self searchAllRecordTable:count MoneyFrom:moneyFrom]];
    while ([record next]) {
        NSMutableDictionary *recordDict = [[NSMutableDictionary alloc] init];
        [recordDict setObject:[record dateForColumn:@"RecordDtate"] forKey:@"RecordDtate"];
        [recordDict setObject:[record stringForColumn:@"MoneyFrom"] forKey:@"MoneyFrom"];
        [recordDict setObject:[record stringForColumn:@"CheckoutType"] forKey:@"CheckoutType"];
        [recordDict setObject:[NSNumber numberWithFloat:[record doubleForColumn:@"Money"]] forKey:@"Money"];
        [recordDict setObject:[record stringForColumn:@"MoneyFor"] forKey:@"MoneyFor"];
        [recordDict setObject:[record stringForColumn:@"Remarks"] forKey:@"Remarks"];
        [array addObject:recordDict];
    }
    return array;
}

#pragma mark - 按时间单位查找记录
- (NSMutableArray *)recordArrayWithDate:(NSDate *)day DataDateType:(DataDateType)dataDatetype
{
    NSMutableArray *array = [[NSMutableArray alloc] init];
    FMResultSet *record = [self.db executeQuery:[self searchRecordWithDate:day DateType:dataDatetype MoneyFor:nil]];
    while ([record next]) {
        NSMutableDictionary *recordDict = [[NSMutableDictionary alloc] init];
        [recordDict setObject:[record dateForColumn:@"RecordDtate"] forKey:@"RecordDtate"];
        [recordDict setObject:[record stringForColumn:@"MoneyFrom"] forKey:@"MoneyFrom"];
        [recordDict setObject:[record stringForColumn:@"CheckoutType"] forKey:@"CheckoutType"];
        [recordDict setObject:[NSNumber numberWithFloat:[record doubleForColumn:@"Money"]] forKey:@"Money"];
        [recordDict setObject:[record stringForColumn:@"MoneyFor"] forKey:@"MoneyFor"];
        [recordDict setObject:[record stringForColumn:@"Remarks"] forKey:@"Remarks"];
        [array addObject:recordDict];
    }
    return array;
}
/*! @brief 统计当月每天或当年每月的记录 */
- (NSMutableArray *)statisticsArrayWithDate:(NSDate *)day DataDateType:(DataDateType)dataDatetype
{
    NSMutableArray *array = [[NSMutableArray alloc] init];
    NSString *selectRecord = @"SELECT RecordDtate AS 日期 ,sum(money),CheckoutType";
    NSString *fromTable = [self searchRecordWithDate:day DateType:dataDatetype MoneyFor:nil];
    NSString *group = @"";
    NSDateFormatter *dateformatter = [[NSDateFormatter alloc] init];
    if (dataDatetype == DataDateTypeMonth) {
        group = @"GROUP BY strftime('%Y-%m-%d',datetime(RecordDtate,'unixepoch','localtime')),CheckoutType";
        [dateformatter setDateFormat:@"YYYY-MM-dd"];
    } else if (dataDatetype == DataDateTypeYear) {
        group = @"GROUP BY strftime('%Y-%m',datetime(RecordDtate,'unixepoch','localtime')),CheckoutType";
        [dateformatter setDateFormat:@"YYYY-MM"];
    }
    NSString *dateCondition = [NSString stringWithFormat:@"%@ FROM (%@) %@",selectRecord,fromTable,group];
    FMResultSet *record = [self.db executeQuery:dateCondition];
    while ([record next]) {
        NSMutableDictionary *recordDict = [[NSMutableDictionary alloc] init];
        [recordDict setObject:[record dateForColumn:@"日期"] forKey:@"RecordDtate"];
        [recordDict setObject:[NSNumber numberWithFloat:0] forKey:@"incomeMoney"];
        [recordDict setObject:[NSNumber numberWithFloat:0] forKey:@"payMoney"];
        if ([[record stringForColumn:@"CheckoutType"] isEqualToString:@"1"]) {
            [recordDict setObject:[NSNumber numberWithFloat:[record doubleForColumn:@"sum(money)"]] forKey:@"incomeMoney"];
        } else {
            [recordDict setObject:[NSNumber numberWithFloat:[record doubleForColumn:@"sum(money)"]] forKey:@"payMoney"];
        }
        BOOL existData = NO;
        for (NSMutableDictionary *dataDict in array) {
            if ([[dateformatter stringFromDate:recordDict[@"RecordDtate"]] isEqualToString:[dateformatter stringFromDate:dataDict[@"RecordDtate"]]]) {
                if ([[record stringForColumn:@"CheckoutType"] isEqualToString:@"1"]) {
                    [dataDict setObject:[NSNumber numberWithFloat:[record doubleForColumn:@"sum(money)"]] forKey:@"incomeMoney"];
                } else {
                    [dataDict setObject:[NSNumber numberWithFloat:[record doubleForColumn:@"sum(money)"]] forKey:@"payMoney"];
                }
                existData = YES;
                break;
            }
        }
        if (!existData) {
            [array addObject:recordDict];
        }
    }
    return array;
}

#pragma mark - 按时间单位查找收支
- (NSMutableDictionary *)incomeAndPayWithDate:(NSDate *)day DataDateType:(DataDateType)dataDatetype
{
    NSMutableDictionary *incomeAndPayDict = [[NSMutableDictionary alloc] init];
    FMResultSet *incomeRecord = [self.db executeQuery:[self searchSumWithDate:day DateType:dataDatetype MoneyFor:nil CheckoutType:YES]];
    while ([incomeRecord next]) {
        [incomeAndPayDict setObject:[[NSNumber alloc] initWithDouble:[incomeRecord doubleForColumnIndex:0]] forKey:@"income"];
    }
    FMResultSet *payRecord = [self.db executeQuery:[self searchSumWithDate:day DateType:dataDatetype MoneyFor:nil CheckoutType:NO]];
    while ([payRecord next]) {
        [incomeAndPayDict setObject:[[NSNumber alloc] initWithDouble:[payRecord doubleForColumnIndex:0]] forKey:@"pay"];
    }
    return incomeAndPayDict;
}

#pragma mark - 按时间单位查找统计
- (NSMutableArray *)rtatisticsIncomeWithDate:(NSDate *)day DataDateType:(DataDateType)dataDatetype
{
    NSMutableArray *rtatisticsArray = [[NSMutableArray alloc] init];
    FMResultSet *incomeRecord = [self.db executeQuery:[self searchGroupMoneyForSumWithDate:day DateType:dataDatetype CheckoutType:YES]];
    while ([incomeRecord next]) {
        NSMutableDictionary *data = [[NSMutableDictionary alloc] init];
        [data setValue:[[NSNumber alloc] initWithDouble:[incomeRecord doubleForColumn:@"SUM(money)"]] forKey:@"dataValue"];
        [data setValue:[incomeRecord stringForColumn:@"MoneyFor"] forKey:@"dataName"];
        [rtatisticsArray addObject:data];
    }
    return rtatisticsArray;
}
- (NSMutableArray *)rtatisticsPayWithDate:(NSDate *)day DataDateType:(DataDateType)dataDatetype
{
    NSMutableArray *rtatisticsArray = [[NSMutableArray alloc] init];
    FMResultSet *payRecord = [self.db executeQuery:[self searchGroupMoneyForSumWithDate:day DateType:dataDatetype CheckoutType:NO]];
    while ([payRecord next]) {
        NSMutableDictionary *data = [[NSMutableDictionary alloc] init];
        [data setValue:[[NSNumber alloc] initWithDouble:[payRecord doubleForColumn:@"SUM(money)"]] forKey:@"dataValue"];
        [data setValue:[payRecord stringForColumn:@"MoneyFor"] forKey:@"dataName"];
        [rtatisticsArray addObject:data];
    }
    return rtatisticsArray;
}

#pragma mark - 搜索内容字符
/*! @brief 按条数搜索记录 */
- (NSString *)searchAllRecordTable:(NSInteger)count MoneyFrom:(NSString *)moneyFrom {
    NSString *searchCondition = @"";
    if (moneyFrom) {
        searchCondition = [NSString stringWithFormat:@"where MoneyFrom = '%@'",moneyFrom];
    }
    if (count == 0) {
        return [NSString stringWithFormat:@"select * from RecordTable %@ order by RecordDtate desc",searchCondition];
    } else {
        return [NSString stringWithFormat:@"select * from RecordTable %@ order by RecordDtate desc limit %@",searchCondition,[@(count) stringValue]];
    }
}
/*! @brief 搜索总额或记录 */
- (NSString *)searchWithSum:(BOOL)sum
{
    if (sum) {
        return @"select SUM(money),MoneyFor from RecordTable";
    } else {
        return @"select * from RecordTable";
    }
}
/*! @brief 搜索总额或记录的条件 */
- (NSString *)searchConditionWithDate:(NSDate *)date DateType:(DataDateType)dateType MoneyFor:(NSString *)moneyFor CheckoutType:(NSString *)checkoutType
{
    NSString *condition = @"";
    NSNumber *dayNumber = [[NSNumber alloc] initWithDouble:date.timeIntervalSince1970];
    NSString *dateCondition;
    switch (dateType) {
        case DataDateTypeDay:
        {
            dateCondition = [NSString stringWithFormat:@"date(datetime(RecordDtate,'unixepoch','localtime')) = date(datetime(%@,'unixepoch','localtime'))",dayNumber];
            break;
        }
        case DataDateTypeMonth:
        {
            NSString *str = @"strftime('%Y-%m',datetime(RecordDtate,'unixepoch','localtime')) = strftime('%Y-%m',";
            dateCondition = [NSString stringWithFormat:@"%@ datetime(%@,'unixepoch','localtime'))",str,dayNumber];
            break;
        }
        case DataDateTypeYear:
        {
            NSString *str = @"strftime('%Y',datetime(RecordDtate,'unixepoch','localtime')) = strftime('%Y',";
            dateCondition = [NSString stringWithFormat:@"%@ datetime(%@,'unixepoch','localtime'))",str,dayNumber];
        }
        default:
            break;
    }
    NSString *moneyForCondition;
    if (moneyFor) {
        moneyForCondition = [NSString stringWithFormat:@"MoneyFor = '%@'",moneyFor];
    } else {
        moneyForCondition = [NSString stringWithFormat:@"MoneyFor <> '%@'",Macro_MoneyForChange];
    }
    if ([dateCondition isEqualToString:@""]) {
        condition = [NSString stringWithFormat:@"where %@",moneyForCondition];
    } else {
        condition = [NSString stringWithFormat:@"where %@ and %@",dateCondition,moneyForCondition];
    }
    if (checkoutType) {
        condition = [NSString stringWithFormat:@"%@ and CheckoutType = %@",condition,checkoutType];
    }
    return condition;
}
/*! @brief 根据条件搜索总额或记录 */
- (NSString *)searchRecordWithDate:(NSDate *)date DateType:(DataDateType)dateType MoneyFor:(NSString *)moneyFor
{
    NSString *searchStr = [self searchWithSum:NO];
    NSString *conditionStr = [self searchConditionWithDate:date DateType:dateType MoneyFor:moneyFor CheckoutType:nil];
    return [NSString stringWithFormat:@"%@ %@",searchStr,conditionStr];
}
- (NSString *)searchSumWithDate:(NSDate *)date DateType:(DataDateType)dateType MoneyFor:moneyFor CheckoutType:(BOOL)checkoutType
{
    NSString *searchStr = [self searchWithSum:YES];
    NSString *conditionStr = [self searchConditionWithDate:date DateType:dateType MoneyFor:moneyFor CheckoutType:checkoutType ? @"1" : @"0"];
    return [NSString stringWithFormat:@"%@ %@",searchStr,conditionStr];
}
#pragma mark - 分组查询
- (NSString *)searchGroupMoneyForSumWithDate:(NSDate *)date DateType:(DataDateType)dateType CheckoutType:(BOOL)checkoutType
{
    NSString *searchStr = [self searchWithSum:YES];
    NSString *conditionStr = [self searchConditionWithDate:date DateType:dateType MoneyFor:nil CheckoutType:checkoutType ? @"1" : @"0"];
    return [NSString stringWithFormat:@"%@ %@ group by MoneyFor order by SUM(money) desc",searchStr,conditionStr];
}

#pragma mark - 更新
- (void)upDataWithMessage:(NSString *)message
{
    NSLog(@"需要更新");
    if (self.delegate) {
        [self.delegate upWalletLocalData:self Message:message];
    }
}

@end
